# Review model
from app.utils.database import get_db_cursor
from datetime import datetime

def create_review(user_id, order_id, rating, comment='', reviewer_name=''):
    """Create review"""
    conn, cursor = get_db_cursor()
    try:
        # Analyze sentiment
        sentiment = analyze_sentiment(comment, rating)
        
        cursor.execute("""
            INSERT INTO reviews (user_id, order_id, rating, comment, sentiment, reviewer_name, created_at, updated_at)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
            RETURNING review_id
        """, (user_id, order_id, rating, comment, sentiment, reviewer_name, datetime.now(), datetime.now()))
        
        review_id = cursor.fetchone()['review_id']
        conn.commit()
        return review_id
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        cursor.close()
        conn.close()

def get_reviews(order_id=None, page=1, per_page=10):
    """Get review list"""
    conn, cursor = get_db_cursor()
    try:
        offset = (page - 1) * per_page
        
        where_clause = ""
        params = []
        
        if order_id:
            where_clause = "WHERE order_id = %s"
            params.append(order_id)
        
        # Get reviews
        cursor.execute(f"""
            SELECT * FROM reviews 
            {where_clause}
            ORDER BY created_at DESC
            LIMIT %s OFFSET %s
        """, params + [per_page, offset])
        
        reviews = [dict(row) for row in cursor.fetchall()]
        
        # Get total count
        cursor.execute(f"SELECT COUNT(*) FROM reviews {where_clause}", params)
        total = cursor.fetchone()['count']
        
        return reviews, total
    finally:
        cursor.close()
        conn.close()

def get_review_by_id(review_id):
    """Get single review"""
    conn, cursor = get_db_cursor()
    try:
        cursor.execute("SELECT * FROM reviews WHERE review_id = %s", (review_id,))
        result = cursor.fetchone()
        return dict(result) if result else None
    finally:
        cursor.close()
        conn.close()

def update_review(review_id, user_id, rating=None, comment=None):
    """Update review"""
    conn, cursor = get_db_cursor()
    try:
        # Build update statement
        updates = []
        params = []
        
        if rating is not None:
            updates.append("rating = %s")
            params.append(rating)
        
        if comment is not None:
            updates.append("comment = %s")
            params.append(comment)
        
        if updates:
            # Re-analyze sentiment
            sentiment = analyze_sentiment(comment or '', rating or 0)
            updates.append("sentiment = %s")
            params.append(sentiment)
            
            updates.append("updated_at = %s")
            params.append(datetime.now())
        
        if not updates:
            return True
        
        params.extend([review_id, user_id])
        cursor.execute(f"""
            UPDATE reviews 
            SET {', '.join(updates)}
            WHERE review_id = %s AND user_id = %s
        """, params)
        
        conn.commit()
        return cursor.rowcount > 0
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        cursor.close()
        conn.close()

def delete_review(review_id, user_id):
    """Delete review"""
    conn, cursor = get_db_cursor()
    try:
        cursor.execute("DELETE FROM reviews WHERE review_id = %s AND user_id = %s", (review_id, user_id))
        conn.commit()
        return cursor.rowcount > 0
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        cursor.close()
        conn.close()

def get_review_stats():
    """Get review statistics"""
    conn, cursor = get_db_cursor()
    try:
        # Overall statistics
        cursor.execute("SELECT COUNT(*) as total_reviews, AVG(rating) as avg_rating FROM reviews")
        stats = cursor.fetchone()
        
        # Statistics by rating
        rating_stats = {}
        for rating in range(1, 6):
            cursor.execute("SELECT COUNT(*) FROM reviews WHERE rating = %s", (rating,))
            rating_stats[rating] = cursor.fetchone()['count']
        
        # Statistics by sentiment
        sentiment_stats = {}
        sentiments = ['positive', 'neutral', 'negative']
        for sentiment in sentiments:
            cursor.execute("SELECT COUNT(*) FROM reviews WHERE sentiment = %s", (sentiment,))
            sentiment_stats[sentiment] = cursor.fetchone()['count']
        
        return {
            'total_reviews': stats['total_reviews'],
            'average_rating': round(float(stats['avg_rating'] or 0), 2),
            'rating_distribution': rating_stats,
            'sentiment_distribution': sentiment_stats
        }
    finally:
        cursor.close()
        conn.close()

def analyze_sentiment(comment, rating):
    """Simple sentiment analysis"""
    if not comment:
        return 'neutral'
    
    # Simple sentiment analysis based on rating
    if rating >= 4:
        return 'positive'
    elif rating <= 2:
        return 'negative'
    else:
        return 'neutral'
